<pre>
<?php

/** 
*
* Sérgio P C Rodrigues <sergiopcr@gmail.com>
* 2010-04
*
*/

$a = set_time_limit(0);
ini_set('memory_limit','3000M');

# Error reporting                              ini_set
error_reporting(E_ALL);

require_once "Classes/PHPExcel/IOFactory.php"; // PHPExcel_IOFactory 
require_once "inputs.php"; // S3DB & Project ID 
require_once "fileRead.php"; // File load, returns 1st row and 1st column
require_once "caches.php";
require_once "countCollections.php";
require_once "dataStructure.php";
require_once "queries.php";
require_once "excelDate.php";


## inputs.php ##


#  call functions



//$numberOfSheets = count($objPHPExcel->getAllSheets());
echo 'Total number of sheets: ' . $numberOfSheets . '. <br />';

for($h = 0 ; $h < $numberOfSheets; $h++){
	
	$objPHPExcel = readExcelFile($excelFile);
	$worksheetIndex = $h;  
	//$worksheetIndex = 1;                                            // iterar worksheet
	$actualWorksheet = readWorkSheet($objPHPExcel, $worksheetIndex);
	$labelsAndRules = getLabelsAndRules($actualWorksheet);
	//echo $_activeSheetIndex;




	# separate labels and rules arrays
	#
	## fileRead.php ## 
	#

	$labels = array(); // all labals in the sheet
	$row1 = array(); //all rules in the sheet

	for ($i = 5; $i < (count($labelsAndRules['labels'])) + 5; $i++)
		$labels[] = $labelsAndRules['labels'][$i];

	for ($i = 1; $i <= count($labelsAndRules['rules']); $i++)
		$row1[] = $labelsAndRules['rules'][$i];




	#### get all sheet data ####

	$data = getData($actualWorksheet, $labels, $row1);
	
	
	#### convert all excel format date values to yyy-mm-dd format ####

	if(is_file($listDateRules)){
		$cacheData = array();		
		$cacheFile = file($listDateRules);
		
		foreach ($row1 as $key1=>$row) {
			foreach ($data as $key2=>$datum){
					if (in_array($row, $cacheFile) && $data[$key2]['values'][$key1] > 2050)
						$data[$key2]['values'][$key1] = excelToDate($data[$key2]['values'][$key1]);	
			}	
		}
	}
	

	#
	# number of collections in the present sheet
	#
	## caches.php ## 
	#

	$s3dbData = collectionsInfo($s3dbDeployment, $key, $projectId, $row1, $cleanRules); // array with collections Id and rules

	$collections = array_unique($s3dbData['collectionId']); //collections in present sheet
	$collec = array();  // <- necessary to empty the array from previous loop
	foreach($collections as $collection)
		$collec[] = $collection;
	$collections = $collec;

	$nCollections = count($collections); // number of collections in present sheet

	echo 'Total number of collections in sheet: ' . $nCollections . '. <br />';



	#
	# get resource_id and description from Isolate collection; get Isolates Labels List
	#
	## caches.php ##
	#

	// List of isolates existing in each collection -> [0]=>mainCollection, [1]=>1st collec, [2]=>2st coll....

	$isolatesList = isolatesList($s3dbDeployment, $key, $projectId, $mainCollectionId, $collections, $cleanIsol);



	//  separate  main collection from others

	$mainCollectionIsolates = $isolatesList[0];             // array[isolateindex](array([resource],[description]))
	$otherCollectionsIsolates = array();                // array[isolateindex](array([resource],[description]))
	for ($i = 1; $i < count($isolatesList); $i++)
		$otherCollectionsIsolates[] = $isolatesList[$i];


	$listMainIsolatesLabels = array(); // array[index]=> labels
	for ($i = 0; $i < count($mainCollectionIsolates); $i++) // for main collection
		$listMainIsolatesLabels[$i]= $mainCollectionIsolates[$i]['description'];  // could have used array_diff

	$listOtherIsolatesLabels = array(); // array[collectionindex][index]=> labels
	for ($i = 0; $i < count($otherCollectionsIsolates); $i++){ // for each collection -> 1st collec, 2st coll....
		$listOtherIsolatesLabels[$i] = array ();
			
		for ($j = 0; $j < count($otherCollectionsIsolates[$i]); $j++){
			$listOtherIsolatesLabels[$i][$j] = array(  );
			if( !empty ($otherCollectionsIsolates[$i] ) )
				$listOtherIsolatesLabels[$i][$j] = $otherCollectionsIsolates[$i][$j]['description'];	
		}
	}


	###### main collection ######

	# find new and existing isolates
	#
	## dataStructure.php ## 
	#
	$sortedIsolates = sortIsolates($listMainIsolatesLabels, $labels); // [newIsolatesLabels], [existingIsolatesLabels] - divides (puts in array) Isolates in  new / existing
	
	
	
	$newMainIsolatesLabels = $sortedIsolates['newIsolatesLabels'];
	$existingMainIsolatesLabels = $sortedIsolates['existingIsolatesLabels'];
		echo'<br /><br />newIsolatesLabels:<br />';
		print_r($newMainIsolatesLabels);
		echo'<br /><br />existingMainIsolatesLabels:<br />';
		print_r($existingMainIsolatesLabels);

	# existing Isolates id from Isolates label
	$existingMainIsolIdLabels = labelsToId($existingMainIsolatesLabels, $mainCollectionIsolates);


	# list of rules from Isolate Collection
	$isolateRules = isolateRules($s3dbDeployment, $key, $projectId, $collections, $cleanRules);

	$newIsolateItems  = insertNewMainIsolates($newMainIsolatesLabels, $mainCollectionId, $s3dbDeployment, $key);

	
	# create an array with all item_id, label info about items of Isolate in the present sheet and collection

	$isolateCollectionItems = array();     // [item_id], [label]  - create an array with new items inserted and existing ones
	$isolateCollectionItems['item_id'] = array_merge($newIsolateItems['item_id'], $existingMainIsolIdLabels['item_id']);
	$isolateCollectionItems['label'] = array_merge($newIsolateItems['label'], $existingMainIsolIdLabels['label']);  


	#### other collections ####
	$ruleIndexNew = 0;
	$ruleIndexExisting = 0;


	// collections iteration

	foreach($collections as $i => $collection){

		
		# get rules from collection
		#
		## # countCollections.php ## 
		#
		//if(isset($rules))
			//unlink($rules);

		$rules = getCollectionRules($collection, $s3dbData);
		

		# find new and existing isolates for each collection found
		#
		## dataStructure.php ## 
		#

		$sortedIsolates = sortIsolates($listOtherIsolatesLabels[$i], $labels); // divides (puts in array) Isolates in  new / existing KEEPS isolates index number!

		echo'<br /><br />collection:<br />';
		print_r($collection);


		$newIsolatesLabels = $sortedIsolates['newIsolatesLabels'];
		$existingIsolatesLabels = $sortedIsolates['existingIsolatesLabels'];

			 
		# existing Isolates id from Isolates label
		$existingIsolatesId = array();
		$existingIsolatesId['item_id'] = array();
		$existingIsolatesId = labelsToId($existingIsolatesLabels, $otherCollectionsIsolates[$i]); 



		echo'<br /><br />newIsolatesLabels:<br />';
		print_r($newIsolatesLabels);
		echo'<br /><br />existingIsolatesLabels:<br />';
		print_r($existingIsolatesLabels);
		echo'<br /><br />existingIsolatesId:<br />';
		print_r($existingIsolatesId);
		echo'<br /><br />isolateCollectionItems:<br />';
		print_r($isolateCollectionItems);




		# Insert Data Into S3DB
		#
		## queries.php ##
		#

		// new Isolates
	
		
		$insertNew = '';
		if(!empty ($newIsolatesLabels)){
			$insertNew = insertNewIsolates($data, $newIsolatesLabels, $s3dbDeployment, $key, $collection, $rules, $isolateCollectionItems, $isolateRules[$i], $ruleIndexNew);
		
		$ruleIndexNew = $insertNew;
		}

		else
			echo '<br /><b>There aren\'t new items to ' . $collection . ' collection.</b><br />';

		
		// existing Isolates
		
		echo '<br /><br />';

		$insertExisting = '';
		if(!empty ($existingIsolatesLabels)){
	
			$insertExisting = insertExistingIsolates($data, $existingIsolatesId, $s3dbDeployment, $key, $collection, $rules, $isolateCollectionItems, $isolateRules[$i], $ruleIndexExisting);
			$ruleIndexExisting = $insertExisting;
		}
			
		else
			echo '<br /><b>There aren\'t new data to existing items from ' . $collection . ' collection.</b><br />';

	}

	echo '<br /><b>Sheet <i>'. $h . '</i>, processed.</b><br />' ;
}
?>
<pre>
